package spark

import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.Connection
import org.apache.spark.SparkConf
import org.apache.spark.streaming.kafka010._
import org.apache.spark.streaming.{Seconds, StreamingContext}
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._
import org.apache.spark.sql.Row // 添加缺少的导入

object OrderStatsSQL4 {
  def main(args: Array[String]): Unit = {

    val conf = new SparkConf().setAppName("OrderStatsSQL").setMaster("local[*]")
    val ssc = new StreamingContext(conf, Seconds(8))
    ssc.sparkContext.setLogLevel("WARN")
    val spark = SparkSession.builder.config(conf).getOrCreate()

    val kafkaParams = Map(
      "bootstrap.servers" -> "192.168.136.128:9092",
      "key.deserializer" -> "org.apache.kafka.common.serialization.StringDeserializer",
      "value.deserializer" -> "org.apache.kafka.common.serialization.StringDeserializer",
      "group.id" -> "niit",
      "auto.offset.reset" -> "earliest"
    )
    import spark.implicits._

    val stream = KafkaUtils.createDirectStream[String, String](
      ssc,
      LocationStrategies.PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](Array("orders"), kafkaParams)
    )

    stream.foreachRDD { rdd =>
      if (!rdd.isEmpty()) {
        // 解析制表符分隔的数据并转换为DataFrame
        val df = rdd.map(record => {
          val fields = record.value().split("\t")
          // 假设字段顺序: category, product, quantity, date, rating, isValid
          (fields(0), fields(6))
        }).toDF("category", "isValid")

        // 统计每个类别的有效和无效数量
        val result = df.groupBy("category", "isValid")
          .agg(count("*").alias("count"))
          .na.fill(0)

        // 显示结果（保留原有逻辑）
        result.show()

        // 将结果写入MySQL
        result.foreachPartition { (partition: Iterator[Row]) => // 显式指定类型
          var conn: Connection = null
          var stmt: PreparedStatement = null
          try {
            // 创建数据库连接
            val url = "jdbc:mysql://localhost:3306/huel2?useSSL=false&serverTimezone=UTC"
            val user = "root"
            val password = "123456"
            conn = DriverManager.getConnection(url, user, password)

            // 批量插入数据
            conn.setAutoCommit(false)
            partition.foreach { row =>
              val category = row.getString(0)
              val isValid = row.getString(1)
              val count = row.getLong(2)

              val sql = "INSERT INTO task4 (category, is_valid, count) VALUES (?,?,?)"
              stmt = conn.prepareStatement(sql)
              stmt.setString(1, category)
              stmt.setString(2, isValid)
              stmt.setLong(3, count)
              stmt.executeUpdate()
              stmt.close()
            }
            conn.commit()
          } catch {
            case e: Exception =>
              e.printStackTrace()
              if (conn != null) conn.rollback()
          } finally {
            if (stmt != null) try { stmt.close() } catch { case e: Exception => e.printStackTrace() }
            if (conn != null) try { conn.close() } catch { case e: Exception => e.printStackTrace() }
          }
        }
      }
    }

    ssc.start()
    ssc.awaitTermination()
  }
}